package com.itheima.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface ReprotMapper extends BaseMapper {


    @Select("select (case when sex=0 then '女' else '男' end) name, count(id) value \n" +
            "from  member_t\n" +
            "group by sex")
    List<Map> memberSexProportion();


    @Select("SELECT COUNT( id ) value,INTERVAL ((SELECT CASE \n" +
            "  WHEN length( id_card )= 18 THEN TIMESTAMPDIFF(YEAR,\n" +
            "\t\tSTR_TO_DATE( substr( id_card, 7, 8 ), '%Y%m%d' ),\n" +
            "\t\tsysdate()) ELSE TIMESTAMPDIFF(YEAR,\n" +
            "\t\tSTR_TO_DATE( concat( '19', substr( id_card, 7, 6 )), '%Y%m%d' ),\n" +
            "\t\tsysdate()) END age ),18,30,45,60 ) AS age_range FROM member_t \n" +
            "WHERE 1 = 1 GROUP BY age_range")
    List<Map> memberAexProportion();
}
